from sqlalchemy import and_, or_, func
from sqlalchemy.orm import sessionmaker, scoped_session
from orm01 import engine, Student
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
# 条件查询
Session.query(Student).filter(Student.age.between(20, 26), Student.name == "菜菜").all()
Session.query(Student).filter(Student.age.in_([24, 25, 26]), Student.name == "菜菜").all()
Session.query(Student).filter(and_(Student.age > 25, Student.name == "菜菜")).all()
Session.query(Student).filter(or_(Student.age > 95, Student.name == "菜菜")).all()
# 通配符
Session.query(Student).filter(Student.name.like("%菜%")).all()
Session.query(Student).filter(~Student.name.like("%菜%")).all()
# 排序
Session.query(Student).filter(Student.name.like("%菜%"))\
        .order_by(Student.age.desc(), Student.name.desc()).all()
# 分组
Session.query(Student.age)\
    .filter(Student.name.like("%菜%")).group_by(Student.age).having(Student.age > 25).all()
# 函数  最大  最小 和 平均等
like__all = Session.query(func.max(Student.age), func.avg(Student.age), func.sum(Student.age))\
    .filter(Student.name.like("%菜%")).group_by(Student.name).all()

for row in like__all:
    print(row)
